package com.xinzhidi.web.equipment.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.Lists;
import com.xinzhidi.common.constant.AgreementConstants;
import com.xinzhidi.common.core.domain.entity.SysDictData;
import com.xinzhidi.common.core.domain.model.LoginUser;
import com.xinzhidi.common.core.page.TableDataInfo;
import com.xinzhidi.common.exception.ServiceException;
import com.xinzhidi.common.utils.DateUtils;
import com.xinzhidi.common.utils.SecurityUtils;
import com.xinzhidi.common.utils.StringUtils;
import com.xinzhidi.common.utils.bean.BeanUtils;
import com.xinzhidi.common.utils.poi.ExcelUtil;
import com.xinzhidi.system.mapper.SysDictDataMapper;
import com.xinzhidi.system.service.ISysDictDataService;
import com.xinzhidi.system.service.ISysDictTypeService;
import com.xinzhidi.web.build.domain.BuildUnit;
import com.xinzhidi.web.build.domain.CBuild;
import com.xinzhidi.web.build.service.IBuildUnitService;
import com.xinzhidi.web.build.service.ICBuildService;
import com.xinzhidi.web.city.controller.TemplateExportUtil;
import com.xinzhidi.web.city.domain.City;
import com.xinzhidi.web.city.service.CityService;
import com.xinzhidi.web.dtuusr.domain.DtuUsr;
import com.xinzhidi.web.dtuusr.service.DtuUsrService;
import com.xinzhidi.web.equipment.domain.Equipment;
import com.xinzhidi.web.equipment.domain.dto.EquipmentAddDto;
import com.xinzhidi.web.equipment.domain.dto.EquipmentDto;
import com.xinzhidi.web.equipment.domain.dto.EquipmentListDto;
import com.xinzhidi.web.equipment.domain.dto.EquipmentUpdDto;
import com.xinzhidi.web.equipment.domain.po.EquipmentPo;
import com.xinzhidi.web.equipment.domain.vo.EquipmentInfoVo;
import com.xinzhidi.web.equipment.domain.vo.EquipmentListVo;
import com.xinzhidi.web.equipment.domain.vo.EquipmentTemperatureVo;
import com.xinzhidi.web.equipment.dto.EquipmentGroupInfoDto;
import com.xinzhidi.web.equipment.mapper.EquipmentMapper;
import com.xinzhidi.web.house.domain.Household;
import com.xinzhidi.web.house.domain.vo.HouseholdExcelVo;
import com.xinzhidi.web.house.service.IHouseholdService;
import com.xinzhidi.web.netty.entity.DeviceInstruction;
import com.xinzhidi.web.netty.utils.SecurityAdminUtils;
import com.xinzhidi.web.plot.domain.entity.CPlot;
import com.xinzhidi.web.plot.service.impl.CPlotServiceImpl;
import lombok.RequiredArgsConstructor;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.validation.Validator;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;


/**
 * 设备Service业务层处理
 *
 * @author cx
 * @date 2023-05-29
 */
@Service
@RequiredArgsConstructor
public class EquipmentService extends ServiceImpl<EquipmentMapper, Equipment> {
    private final EquipmentMapper equipmentMapper;

    private final ISysDictTypeService sysDictTypeService;

    private final CPlotServiceImpl plotService;

    private final ICBuildService buildService;

    private final IBuildUnitService unitService;

    private final IHouseholdService householdService;

    protected final Validator validator;

    private final ISysDictDataService sysDictDataService;

    private final DtuUsrService dtuUsrService;

    private final SysDictDataMapper sysDictDataMapper;

    private final CityService cityService;

    @Autowired
    private ISysDictTypeService dictTypeService;


    /**
     * 查询设备
     *
     * @param id 设备主键
     * @return 设备
     */
    public EquipmentListVo selectEquipmentById(Long id) {
        EquipmentListDto dto = new EquipmentListDto();
        dto.setId(id);
        EquipmentListVo vo = new EquipmentListVo();
        List<EquipmentListVo> list = equipmentMapper.selectEquipmentListVo(dto, Lists.newArrayList());
        if (CollectionUtils.isNotEmpty(list)) {
            BeanUtils.copyProperties(list.get(0), vo);
        }
        return vo;
    }

    /**
     * 查询设备列表
     *
     * @param dto 查询设备dto
     * @return 设备
     */
    public TableDataInfo<EquipmentListVo> selectEquipmentList(EquipmentListDto dto) {

        List<Long> userPlotIds = SecurityAdminUtils.getUserPlotIds();
        List<EquipmentListVo> list = equipmentMapper.selectEquipmentListVo(dto, userPlotIds);
        Map<String, String> equipmentTypeIdMapName = sysDictTypeService.selectDictDataByType("equipment_type").stream().collect(Collectors.toMap(SysDictData::getDictValue, SysDictData::getDictLabel));
        Map<String, String> protocolTypeIdMapName = sysDictTypeService.selectDictDataByType("protocol_type").stream().collect(Collectors.toMap(SysDictData::getDictValue, SysDictData::getDictLabel));
        return TableDataInfo.getDataTable(list, vo -> {
            vo.setEquipmentTypeName(equipmentTypeIdMapName.get(vo.getEquipmentType()));
            vo.setProtocolName(protocolTypeIdMapName.get(vo.getProtocolType()));
            return vo;
        });
    }

    /**
     * 查询设备列表(不分页)
     *
     * @param dto 查询设备dto
     * @return 设备
     */
    public List<EquipmentListVo> listEquipmentNoPage(EquipmentDto dto) {
        List<EquipmentListVo> list = equipmentMapper.listEquipmentNoPage(dto);
        return list;
    }

    /**
     * 新增设备
     *
     * @param dto 新增设备dto
     * @return 结果
     */
    public int insertEquipment(EquipmentAddDto dto) {
        this.checkExist(null, dto.getEquipmentCode(), dto.getEquipmentName(), dto.getProtocolType(), dto.getAddress());
        LoginUser loginUser = SecurityUtils.getLoginUser();
        checkEquipment(dto.getHouseholdId(), dto.getProtocolType(), null);
        Equipment equipment = new Equipment();
        BeanUtils.copyProperties(dto, equipment);
        equipment.setCreateBy(loginUser.getUserId().toString());
        equipment.setCreateTime(DateUtils.getNowDate());
        LambdaQueryWrapper<Equipment> wrapper = new LambdaQueryWrapper<>();
        wrapper.ne(dto.getLotId() != null, Equipment::getLotId, dto.getLotId());
        return equipmentMapper.insert(equipment);
    }


    /**
     * 修改设备
     *
     * @param dto 修改设备dto
     * @return 结果
     */
    public int updateEquipment(EquipmentUpdDto dto) {
        this.checkExist(dto.getId(), dto.getEquipmentCode(), dto.getEquipmentName(), dto.getProtocolType(), dto.getAddress());
        LoginUser loginUser = SecurityUtils.getLoginUser();
        checkEquipment(dto.getHouseholdId(), dto.getProtocolType(), dto.getId());
        Equipment equipment = new Equipment();
        BeanUtils.copyProperties(dto, equipment);
        equipment.setUpdateBy(loginUser.getUserId().toString());
        equipment.setUpdateTime(DateUtils.getNowDate());
        return equipmentMapper.updateById(equipment);
    }

    /**
     * 校验设备编码或设备名称是否存在
     *
     * @param id            设备id
     * @param equipmentCode 设备编码
     * @param equipmentName 设备名称
     * @param protocolType  协议类型
     */
    public void checkExist(Long id, String equipmentCode, String equipmentName, String protocolType, String address) {

        if (StringUtils.isBlank(address)) {
            throw new ServiceException("设备地址不能为空！");
        }
        LambdaQueryWrapper<Equipment> wrapper = Wrappers.lambdaQuery();
        wrapper.eq(Equipment::getAddress, address);
        wrapper.ne(id != null, Equipment::getId, id);
        Equipment equipment = this.getOne(wrapper);
        if (equipment != null) {
            throw new ServiceException("设备地址【" + address + "】已存在！");
        }

        if (StringUtils.isNotBlank(equipmentCode)) {
            equipmentCode = equipmentCode.trim();
            wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(StringUtils.isNotBlank(equipmentCode), Equipment::getEquipmentCode, equipmentCode);
            wrapper.eq(StringUtils.isNotBlank(protocolType), Equipment::getProtocolType, protocolType);
            wrapper.ne(id != null, Equipment::getId, id);
            List<Equipment> equipmentList = this.list(wrapper);
            if (CollectionUtils.isNotEmpty(equipmentList)) {
                throw new ServiceException("设备编码【" + equipmentCode + "】已存在！");
            }
        }
        if (StringUtils.isNotBlank(equipmentName)) {
            wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(StringUtils.isNotBlank(equipmentName), Equipment::getEquipmentName, equipmentName);
            wrapper.ne(id != null, Equipment::getId, id);
            if (CollectionUtils.isNotEmpty(list(wrapper))) {
                throw new ServiceException("设备名称【" + equipmentName + "】已存在！");
            }
        }


    }

    // 判断住户 是否添加过设备
    public void checkEquipment(Long householdId, String protocolType, Long id) {
        if (householdId == null) {
            return;
        }
        LambdaQueryWrapper<Equipment> wrapper = new LambdaQueryWrapper<>();
        wrapper.clear();
        wrapper.eq(Equipment::getHouseholdId, householdId);
        wrapper.eq(Equipment::getProtocolType, protocolType);
        wrapper.ne(id != null, Equipment::getId, id);
        Equipment equipment = equipmentMapper.selectOne(wrapper);
        if (equipment != null) {
            throw new ServiceException("当前住户已经添加该设备协议，不能重复添加!");
        }
    }


    /**
     * 批量删除设备
     *
     * @param ids 需要删除的设备主键
     * @return 结果
     */
    public int deleteEquipmentByIds(Long[] ids) {
        return equipmentMapper.deleteBatchIds(Arrays.asList(ids));
    }

    /**
     * 删除设备信息
     *
     * @param id 设备主键
     * @return 结果
     */
    public int deleteEquipmentById(Long id) {
        return equipmentMapper.deleteById(id);
    }

    /**
     * 根据住户id及设备类型查询设备信息
     *
     * @param householdId  住户id
     * @param protocolType 设备类型  1：户用阀门协议 2：单元阀门协议 3：室温采集器协议 4：户用热量表协议 5：单元热量表协议
     */
    public Equipment getEquipment(Long householdId, String protocolType) {
        LambdaQueryWrapper<Equipment> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(Equipment::getHouseholdId, householdId)
                .eq(Equipment::getProtocolType, protocolType);
        Equipment equipment = this.getOne(wrapper);
        return equipment;
    }

    /**
     * 获取设备list
     *
     * @param equipmentPo 设备查询条件
     * @return 设备信息
     */
    public List<EquipmentInfoVo> getEquipmentList(EquipmentPo equipmentPo) {
        return this.baseMapper.getEquipmentList(equipmentPo);
    }

    /**
     * 根据dtuId和address获取设备
     *
     * @param dtuId   dtuId
     * @param address 设备地址
     * @return 设备信息
     */
    public EquipmentInfoVo getEquipmentInfoVo(Long dtuId, String address) {
        EquipmentPo equipmentPo = new EquipmentPo();
        equipmentPo.setDtuId(dtuId);
        equipmentPo.setAddress(address);
        List<EquipmentInfoVo> equipmentList = this.baseMapper.getEquipmentList(equipmentPo);
        if (CollectionUtils.isEmpty(equipmentList)) {
            throw new ServiceException("设备不存在！");
        }
        return equipmentList.get(0);
    }

    /**
     * 根据楼栋id获取楼栋设备list  包括楼栋设备及住户设备
     *
     * @param buildIds 楼栋id集合
     * @return 设备信息
     */
    public List<EquipmentInfoVo> getBuildEquipmentList(List<Long> buildIds) {
        List<EquipmentInfoVo> equipmentList = Lists.newArrayList();
        if (CollectionUtils.isEmpty(buildIds)) {
            return equipmentList;
        }
        //查询楼栋下的单元设备
        equipmentList = this.baseMapper.getUnitEquipmentList(buildIds, AgreementConstants.AgreementType.UNIT_VALVE);
        return equipmentList;
    }

    /**
     * 根据单元id获取单元设备list  包括单元设备及住户设备
     *
     * @param unitIds 单元id集合
     * @return 设备信息
     */
    public List<EquipmentInfoVo> getHouseholdEquipmentList(List<Long> unitIds) {
        List<EquipmentInfoVo> equipmentList = Lists.newArrayList();
        if (CollectionUtils.isEmpty(unitIds)) {
            return equipmentList;
        }
        //查询楼栋下的单元设备
        equipmentList = this.baseMapper.getHouseholdEquipmentList(unitIds, AgreementConstants.AgreementType.UNIT_VALVE);
        return equipmentList;
    }

    /**
     * 通过不同维度id获取该维度下的设备  如：小区id、楼栋id等。
     * 如果传的是小区id，则获取小区下的所有设备，包括楼栋设备、单元设备、住户设备。其他维度同理
     *
     * @param ids      维度id集合
     * @param setType  来源（1：平衡调控  2：初始设置）   如果source不为空，说明是平台调控。是空说明是初始设置。平台调控获取设备的时候不包含维度的下级设备
     * @param dataType 维度类型 0：小区 1：楼栋 2：单元 3：住户
     * @return 设备信息list
     */
    public List<EquipmentInfoVo> getEquipmentListByDim(String setType, String dataType, List<Long> ids) {
        List<EquipmentInfoVo> equipmentList = Lists.newArrayList();
        if (CollectionUtils.isEmpty(ids)) {
            return equipmentList;
        }
        equipmentList = this.baseMapper.getEquipmentListByDim(setType, dataType, ids);
        return equipmentList;
    }

    /**
     * 判断某个级别的id（如：小区、楼栋等的id）下是否有设备
     *
     * @param equipmentLevel 设备级别（0：小区  1：楼栋  2：单元  3：住户）
     * @param id             级别id
     * @param protocolType   协议类型  1：户用阀门协议 2：单元阀门协议 3：室温采集器协议 4：户用热量表协议 5：单元热量表协议
     * @return 设备信息
     */
    public List<Equipment> getEquipmentByParam(String equipmentLevel, Long id, String protocolType) {
        LambdaQueryWrapper<Equipment> wrapper = new LambdaQueryWrapper<>();

        if (equipmentLevel.equals(AgreementConstants.DataType.PLOT)) {
            wrapper.eq(Equipment::getId, id);
        } else if (equipmentLevel.equals(AgreementConstants.DataType.BUILD)) {
            wrapper.eq(Equipment::getBuildId, id);
        } else if (equipmentLevel.equals(AgreementConstants.DataType.BUILD_UNIT)) {
            wrapper.eq(Equipment::getUnitId, id);
        } else if (equipmentLevel.equals(AgreementConstants.DataType.HOUSEHOLD)) {
            wrapper.eq(Equipment::getHouseholdId, id);
        }
        wrapper.eq(StringUtils.isNotBlank(equipmentLevel), Equipment::getEquipmentLevel, equipmentLevel);
        wrapper.eq(StringUtils.isNotBlank(equipmentLevel), Equipment::getProtocolType, protocolType);
        return this.list(wrapper);

    }

    public String importEquipment(List<HouseholdExcelVo> equipmentList) {
        List<Integer> errorList = Lists.newArrayList();
        List<HouseholdExcelVo> voList = Lists.newArrayList();
        //基础校验数据
        if (StringUtils.isNull(equipmentList) || equipmentList.size() == 0) {
            throw new ServiceException("导入设备数据不能为空！");
        }
        Integer totalNumber = equipmentList.size();
        //协议协议类型字典数据
        List<SysDictData> protocolTypeDictDataList = sysDictDataMapper.selectDictDataByType("protocol_type");
        SysDictData sysDictData;
        String protocolType;
        StringBuilder successMsg = new StringBuilder();
        StringBuilder failureMsg = new StringBuilder();
        int failureNum = 0;
        Integer sortNum = 0;
        //用于存放有问题的excel数据的设备编号
        List<String> numCodes = Lists.newArrayList();
        Long plotId = null;
        CPlot cPlot;
        Long buildId;
        CBuild build = null;
        Long unitId;
        BuildUnit unit = null;
        Household household;
        String plotName;
        String createBy = SecurityUtils.getLoginUser().getUserId().toString();
        LambdaQueryWrapper<DtuUsr> dtuWrapper;
        for (int i = 0; i < equipmentList.size(); i++) {
            boolean dataFlag = true;

            Integer num = i + 1;

            String equipmentLevel = "0";

            HouseholdExcelVo vo = equipmentList.get(i);
            plotName = vo.getLotName();

            cPlot = plotService.getOne(new LambdaQueryWrapper<CPlot>(CPlot.class).eq(CPlot::getParkName, plotName));
            if (cPlot == null) {
                sortNum += 1;
                failureMsg.append("<br/>" + sortNum + "、第" + num + "行小区名称不存在！");
                errorList.add(i);
                failureNum+=1;
                equipmentList.remove(i);
                continue;
            }
            plotId = cPlot.getId();
            vo.setPlotId(cPlot.getId());

            buildId = vo.getBuildId();
            unitId = vo.getUnitId();
            sysDictData = protocolTypeDictDataList.stream().filter(x -> x.getDictLabel().equals(vo.getProtocolName())).findFirst().orElse(null);
            protocolType = sysDictData.getDictValue();


            DtuUsr dtuUsr;
            //是否为室温采集器协议  true:是  false:否
            boolean flag = vo.getProtocolName().equals(AgreementConstants.AgreementTypeName.GREENHOUSE_COLLECTOR);
            if (!flag) {
                dtuWrapper = new LambdaQueryWrapper<>();
                dtuWrapper.eq(DtuUsr::getName, vo.getDtuName());
                dtuUsr = dtuUsrService.getOne(dtuWrapper);
                if (dtuUsr == null) {
                    sortNum += 1;
                    failureMsg.append("<br/>" + sortNum + "、第" + num + "行dtu名称不存在！");
                    errorList.add(i);
                    failureNum+=1;
                    equipmentList.remove(i);
                    continue;
                }
            }

            try {
                checkExist(null, vo.getEquipmentCode(), vo.getEquipmentName(), protocolType, vo.getAddress());
            } catch (Exception e) {
                errorList.add(i);
                sortNum += 1;
                failureMsg.append("<br/>" + sortNum + "、第" + num + "行，" + e.getMessage());
                numCodes.add(vo.getAddress());
                dataFlag = false;
            }

//            checkEquipment(vo.getHouseholdId(), protocolType, null);

            String buildName = vo.getBuildName();
            String unitName = vo.getUnitName();
            String houseNumber = vo.getHouseNumber();


            if (StringUtils.isNotBlank(buildName)) {
                build = buildService.getOne(new LambdaQueryWrapper<CBuild>(CBuild.class).eq(CBuild::getPlotId, cPlot.getId()).eq(CBuild::getBuildName, buildName));
                if (build == null) {
                    build = new CBuild();
                    build.setCreateBy(createBy);
                    build.setCreateTime(DateUtils.getNowDate());
                } else {
                    build.setUpdateBy(createBy);
                    build.setUpdateTime(DateUtils.getNowDate());
                }
                build.setPlotId(cPlot.getId());
                build.setBuildName(buildName);
                build.setUpdateBy(createBy);
                build.setUpdateTime(DateUtils.getNowDate());
                buildService.saveOrUpdate(build);
                buildId = build.getId();
                vo.setBuildId(build.getId());
                equipmentLevel = "1";
            }

            if (StringUtils.isNotBlank(unitName)) {
                unit = unitService.getOne(new LambdaQueryWrapper<BuildUnit>(BuildUnit.class).eq(BuildUnit::getBuildId, buildId).eq(BuildUnit::getUnitName, unitName));
                if (unit == null) {
                    unit = new BuildUnit();
                    unit.setCreateBy(createBy);
                    unit.setCreateTime(DateUtils.getNowDate());
                    unit.setLotId(plotId);
                    unit.setBuildId(buildId);
                    unit.setUnitName(unitName);
                } else {
                    unit.setUpdateBy(createBy);
                    unit.setUpdateTime(DateUtils.getNowDate());
                }
                unit.setFloorNumber(vo.getFloorNumber());
                unitService.saveOrUpdate(unit);
                unitId = unit.getId();
                vo.setUnitId(unitId);
                equipmentLevel = "2";
            }

            if (StringUtils.isNotBlank(houseNumber)) {
                household = householdService.getOne(new LambdaQueryWrapper<Household>(Household.class).eq(Household::getUnit, unitId).eq(Household::getHouseNumber, StringUtils.getInteger(build.getBuildName())+"-"+StringUtils.getInteger(unit.getUnitName())+"-"+houseNumber));
                if (household == null || household.getId() == null) {
                    household = new Household();
                    household.setCreateBy(createBy);
                    household.setCreateTime(DateUtils.getNowDate());
                }
                household.setPlotId(plotId);
                household.setBuildId(buildId);
                household.setUnit(unitId);
                household.setFloor(Long.valueOf(vo.getHouseNumber().substring(0,1)));
                household.setHouseNumber(StringUtils.getInteger(build.getBuildName())+"-"+StringUtils.getInteger(unit.getUnitName())+"-"+houseNumber);
                householdService.saveOrUpdate(household);
                vo.setHouseholdId(household.getId());
                equipmentLevel = "3";
            }
            vo.setEquipmentLevel(equipmentLevel);
            vo.setEquipmentType(this.getEquipmentType(protocolType));
            if (!dataFlag) {
                failureNum++;
            }
        }

        if (failureNum > 1) {
            equipmentList = equipmentList.stream().filter(x -> !numCodes.contains(x.getEquipmentCode())).collect(Collectors.toList());
        }

        //开始导入
        int successNum = 0;
        DtuUsr dtuUsr = null;

        Long userId = SecurityUtils.getLoginUser().getUserId();
        for (int i = 0; i < equipmentList.size(); i++) {
            if (errorList.contains(i)) {
                continue;
            }
            HouseholdExcelVo vo = equipmentList.get(i);
            Integer num = i + 1;

            //是否为室温采集器协议  true:是  false:否
            boolean flag = vo.getProtocolName().equals(AgreementConstants.AgreementTypeName.GREENHOUSE_COLLECTOR);
            try {
                //是否是海龙马调节阀
                LambdaQueryWrapper<Equipment> equipmentWrapper = Wrappers.lambdaQuery();
                equipmentWrapper.eq(Equipment::getAddress, vo.getAddress());
                if (!flag) {
                    dtuWrapper = new LambdaQueryWrapper<>();
                    dtuWrapper.eq(DtuUsr::getName, vo.getDtuName());
                    dtuUsr = dtuUsrService.getOne(dtuWrapper);
                }
                Equipment entity = this.getOne(equipmentWrapper);

                if (entity != null) {
                    sortNum += 1;
                    failureMsg.append("<br/>" + sortNum + "、第" + num + "行设备编码或地址已存在！");
                    failureNum+=1;
                    equipmentList.remove(i);
                    continue;
                } else {
                    entity = new Equipment();
                    BeanUtils.copyProperties(vo, entity);
                    if (!flag) {
                        entity.setDtuId(dtuUsr.getId());
                    } else {
                        entity.setDtuId(0L);
                    }
                    entity.setLotId(vo.getPlotId());
                    entity.setBuildId(vo.getBuildId());
                    entity.setUnitId(vo.getUnitId());
                    entity.setHouseholdId(vo.getHouseholdId());
                    entity.setCreateBy(userId.toString());
                }

                String protocolTypeName = vo.getProtocolName();
                String dictValue = sysDictDataService.selectDictValue("protocol_type", protocolTypeName);
                if (StringUtils.isBlank(dictValue)) {
                    sortNum += 1;
                    failureNum+=1;
                    equipmentList.remove(i);
                    throw new ServiceException("第" + num + "行设备 " + vo.getHouseNumber() + " 协议类型不存在");
                }
                entity.setProtocolType(dictValue);


//                String equipmentTypeName = vo.getEquipmentTypeName();
//                String equipmentType = sysDictDataService.selectDictValue("equipment_type", equipmentTypeName);
//                if (StringUtils.isBlank(equipmentType)) {
//                    throw new ServiceException("第" + num + "行设备 " + equipmentTypeName + " 设备类型不存在");
//                }
//                entity.setEquipmentType(equipmentType);

                this.save(entity);
                successNum++;
            } catch (Exception e) {
                failureNum++;
                String msg = "<br/>" + sortNum + "、第" + num + "行设备 " + vo.getHouseNumber() + " 导入失败";
                failureMsg.append(msg + e.getMessage());
                log.error(msg, e);
            }
        }

        if (failureNum == 0) {
            successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条。");
        } else if (totalNumber == failureNum) {
            failureMsg.insert(0, "导入失败,错误如下：");
            throw new ServiceException(failureMsg.toString());
        } else {
            failureMsg.insert(0, "恭喜您，数据已部分导入成功！共 " + totalNumber + " 条，失败 " + failureNum + " 条,错误如下：");
            throw new ServiceException(failureMsg.toString());
        }
        return successMsg.toString();
    }

    public String getEquipmentType(String protocolType) {
        if (protocolType.equals(AgreementConstants.AgreementType.HOUSEHOLD_VALVE)
                || protocolType.equals(AgreementConstants.AgreementType.UNIT_VALVE)
                || protocolType.equals(AgreementConstants.AgreementType.BUILD_VALVE)
                || protocolType.equals(AgreementConstants.AgreementType.SEAHORSE_VALVE_METER)) {
            return AgreementConstants.EquipmentType.VALVE;
        } else if (protocolType.equals(AgreementConstants.AgreementType.GREENHOUSE_COLLECTOR)) {
            return AgreementConstants.EquipmentType.TEMPERATURE_COLLECTOR;
        }
        return AgreementConstants.EquipmentType.HEAT_METER;
    }

    public TableDataInfo<EquipmentListVo> equipmentGroupInfo(EquipmentGroupInfoDto dto) {
        List<EquipmentListVo> equipmentInfoVos = equipmentMapper.equipmentGroupInfo(dto);
        return TableDataInfo.getDataTable(equipmentInfoVos);
    }

    public void removeGroup(Integer equipmentId) {
        LambdaUpdateWrapper<Equipment> updateWrapper = Wrappers.lambdaUpdate();
        updateWrapper.eq(Equipment::getId, equipmentId).set(Equipment::getEquipmentGroupId, null);
        equipmentMapper.update(null, updateWrapper);
    }

    public Equipment getEquipmentByModelIds() {
        return null;
    }

    public List<DeviceInstruction> getReconnectAddress() {
        return equipmentMapper.getReconnectAddress();
    }

    public EquipmentTemperatureVo getTemperatureEquipment(Long householdId) {
        return equipmentMapper.getTemperatureEquipment(householdId);
    }

    public Integer getNoSendEquipmentNumber(String field, Long id, String equipmentLevel, List<String> protocolTypeList) {
        return equipmentMapper.getNoSendEquipmentNumber(field, id, equipmentLevel, protocolTypeList);
    }

    public HSSFWorkbook exportTemplate() {
        HSSFWorkbook book = new HSSFWorkbook();
        String[] titleNames = new String[]{"省", "市", "区", "dtu名称", "协议类型", "设备名称", "设备编号", "设备地址", "是否主动抄表", "小区名称", "详细地址", "小区热计量方式", "供热用户", "面积单价", "热量单价", "供热公司", "所属物业", "自动抄表时间间隔", "楼栋名称", "楼栋热计量方式", "单元名称", "楼层数", "单元热计量方式", "住户姓名", "楼层号", "门牌号", "方位", "管道位置", "是否供暖", "采暖面积", "房间热卡号", "固定电话", "移动电话"};
        // 创建需要用户填写的sheet
        HSSFSheet sheetPro = (HSSFSheet) book.createSheet("设备导入");
        Row row0 = sheetPro.createRow(0);
        Row row1 = sheetPro.createRow(1);
        CellStyle cellStyle = book.createCellStyle(); // 创建单元格样式对象
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); // 使用颜色索引设置背景颜色

        Cell styleCell;
        for (int i = 0; i < titleNames.length; i++) {
            sheetPro.setColumnWidth(i, 20 * 256); // 设置第一列的宽度为20个字符宽度
            styleCell = row0.createCell(i);
            styleCell.setCellValue(titleNames[i]);
            styleCell.setCellStyle(cellStyle);
        }
        sheetPro.createFreezePane(0, 1, 0, 1);
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); // 设置背景色为黄色

        List<City> List = cityService.list();

        List<DtuUsr> dtuUsrList = dtuUsrService.list();
        List<String> dtuNames = dtuUsrList.stream().map(DtuUsr::getName).collect(Collectors.toList());
        String[] comboBoxs = dtuNames.toArray(new String[]{});


        List<SysDictData> protocolNameData = dictTypeService.selectDictDataByType("protocol_type");
        List<String> protocolNames = protocolNameData.stream().map(SysDictData::getDictLabel).collect(Collectors.toList());
        String[] protocolComboBoxs = protocolNames.toArray(new String[]{});

        List<SysDictData> heatMeterData = dictTypeService.selectDictDataByType("sys_heat_meter");
        List<String> heatMeters = heatMeterData.stream().map(SysDictData::getDictLabel).collect(Collectors.toList());
        String[] heatMeterComboBoxs = heatMeters.toArray(new String[]{});

        List<String> autoReads = Lists.newArrayList("是", "否");
        String[] autoReadsComboBoxs = autoReads.toArray(new String[]{});


        //设置dtu下拉
        ExcelUtil.setSpinnerDataGt256(book, sheetPro, "dtuSheet", 1, ExcelUtil.sheetSize, 3, 3, comboBoxs);
        //设置协议类型下拉
        ExcelUtil.setSpinnerDataGt256(book, sheetPro, "PlotProtocolSheet", 1, ExcelUtil.sheetSize, 4, 4, protocolComboBoxs);
        //设置是否主动抄表下拉
        ExcelUtil.setSpinnerDataGt256(book, sheetPro, "autoReadSheet", 1, ExcelUtil.sheetSize, 8, 8, autoReadsComboBoxs);
        //设置小区热计量方式下拉
        ExcelUtil.setSpinnerDataGt256(book, sheetPro, "PlotHeatMeterSheet", 1, ExcelUtil.sheetSize, 11, 11, heatMeterComboBoxs);
        //设置楼栋热计量方式下拉
        ExcelUtil.setSpinnerDataGt256(book, sheetPro, "buildHeatMeterSheet", 1, ExcelUtil.sheetSize, 19, 19, heatMeterComboBoxs);
        //设置单元热计量方式下拉
        ExcelUtil.setSpinnerDataGt256(book, sheetPro, "unitHeatMeterSheet", 1, ExcelUtil.sheetSize, 22, 22, heatMeterComboBoxs);
        //设置是否供暖下拉
        ExcelUtil.setSpinnerDataGt256(book, sheetPro, "gnSheet", 1, ExcelUtil.sheetSize, 28, 28, autoReadsComboBoxs);
        row1.createCell(0).setCellValue("四川省");
        row1.createCell(1).setCellValue("南充市");
        row1.createCell(2).setCellValue("顺庆区");
        row1.createCell(3).setCellValue(dtuNames.get(0));
        row1.createCell(4).setCellValue(protocolNameData.get(0).getDictLabel());
        row1.createCell(5).setCellValue("户用阀门设备1");
        row1.createCell(6).setCellValue("1");
        row1.createCell(7).setCellValue("41580643000001");
        row1.createCell(8).setCellValue("是");
        row1.createCell(9).setCellValue("花间苑");
        row1.createCell(10).setCellValue("四川省南充市顺庆区花间苑");
        row1.createCell(11).setCellValue(heatMeterData.get(0).getDictLabel());
        row1.createCell(12).setCellValue("350");
        row1.createCell(13).setCellValue("131000");
        row1.createCell(14).setCellValue("2.5");
        row1.createCell(15).setCellValue("顺庆供热公司");
        row1.createCell(16).setCellValue("成都物业");
        row1.createCell(17).setCellValue("2");
        row1.createCell(18).setCellValue("1号楼");
        row1.createCell(19).setCellValue(heatMeterData.get(0).getDictLabel());
        row1.createCell(20).setCellValue("1单元");
        row1.createCell(21).setCellValue("31");
        row1.createCell(22).setCellValue(heatMeterData.get(0).getDictLabel());
        row1.createCell(23).setCellValue("xxx");
        row1.createCell(24).setCellValue("7");
        row1.createCell(25).setCellValue("7-1-1001");
        row1.createCell(26).setCellValue("坐北朝南");
        row1.createCell(27).setCellValue("厅及楼梯等部位");
        row1.createCell(28).setCellValue("是");
        row1.createCell(29).setCellValue("90.5");
        row1.createCell(30).setCellValue("12546101");
        row1.createCell(31).setCellValue("2792168");
        row1.createCell(32).setCellValue("15008237107");

        // 查询所有的省名称
        List<City> provinceArr = List.stream().filter(city -> city.getPid() == 0).collect(Collectors.toList());
        Map<String, List<String>> areaMap = new HashMap();
        List<City> cityArr = List.stream().filter(city -> city.getId().endsWith("00")).collect(Collectors.toList());

        List<City> areaArr = List.stream().filter(city -> !city.getId().endsWith("00")).collect(Collectors.toList());

        List<String> sitList;
        for (City p : provinceArr) {
            sitList = List.stream().filter(city -> String.valueOf(city.getPid()).equals(p.getId())).map(City::getName).collect(Collectors.toList());
            areaMap.put(p.getName(), sitList);
        }

        for (City c : cityArr) {
            sitList = List.stream().filter(city -> String.valueOf(city.getPid()).equals(c.getId())).map(City::getName).collect(Collectors.toList());
            areaMap.put(c.getName(), sitList);
        }


        // 创建一个专门用来存放地区信息的隐藏sheet页
        // 因此也不能在现实页之前创建，否则无法隐藏。
        Sheet hideSheet = book.createSheet("area");

        int rowId = 0;
        // 设置第一行，存省的信息
        Row provinceRow = hideSheet.createRow(rowId++);
        provinceRow.createCell(0).setCellValue("省列表");
        for (int i = 0; i < provinceArr.size(); i++) {
            Cell provinceCell = provinceRow.createCell(i + 1);
            provinceCell.setCellValue(provinceArr.get(i).getName());
        }
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        Iterator<String> keyIterator = areaMap.keySet().iterator();
        while (keyIterator.hasNext()) {
            String key = keyIterator.next();
            List<String> son = areaMap.get(key);
            Row row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int i = 0; i < son.size(); i++) {
                Cell cell = row.createCell(i + 1);
                cell.setCellValue(son.get(i));
            }
            // 添加名称管理器
            String range = ExcelUtil.getRange(1, rowId, son.size());
            Name name = book.createName();
            // key不可重复
            name.setNameName(key);
            String formula = "area!" + range;
            name.setRefersToFormula(formula);
        }

        book.setSheetHidden(book.getSheetIndex("area"), true);

        // 省规则
        DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(provinceArr.stream().map(City::getName).collect(Collectors.toList()).toArray(new String[]{}));
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, ExcelUtil.sheetSize, 0, 0);
        DataValidation provinceDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint);
        provinceDataValidation.createErrorBox("error", "请选择正确的省份");
        sheetPro.addValidationData(provinceDataValidation);
        // 对前20行设置有效性
        for (int i = 2; i < 62514; i++) {
            TemplateExportUtil.setDataValidation(TemplateExportUtil.NumToExcel(0), sheetPro, i, 2);
            TemplateExportUtil.setDataValidation(TemplateExportUtil.NumToExcel(1), sheetPro, i, 3);
        }
        return book;
    }


    public void setBox() {
        //设置dtu名称
        setDtuName();
        setPlotBox();
        //设置楼栋热计量方式
//        setBuildHeatMeter();
        //设置协议类型
//        setProtocolType();
        //设置小区热计量方式
//        setPlotUnitHeatMeter();
        //设置单元热计量方式
//        setBuildUnitHeatMeter();
        //设置是否供暖
//        setHeatingOrNot();


    }


    public void setPlotBox() {
        List<CPlot> plotList = plotService.list();
        List<String> list = plotList.stream().map(CPlot::getParkName).collect(Collectors.toList());
        Field file;
        try {
            file = HouseholdExcelVo.class.getDeclaredField("lotName");
            ExcelUtil.setCombo(file, list);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void setDtuName() {
        List<DtuUsr> data = dtuUsrService.list();
        if (org.apache.commons.collections.CollectionUtils.isEmpty(data)) {
            return;
        }
        List<String> list = data.stream().map(DtuUsr::getName).collect(Collectors.toList());
        Field file;
        try {
            file = HouseholdExcelVo.class.getDeclaredField("dtuName");
            ExcelUtil.setCombo(file, list);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void setProtocolType() {
        List<SysDictData> data = dictTypeService.selectDictDataByType("protocol_type");
        if (org.apache.commons.collections.CollectionUtils.isEmpty(data)) {
            return;
        }
        List<String> list = data.stream().map(SysDictData::getDictLabel).collect(Collectors.toList());
        Field file;
        try {
            file = HouseholdExcelVo.class.getDeclaredField("protocolName");
            ExcelUtil.setCombo(file, list);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void setBuildHeatMeter() {
        List<SysDictData> data = dictTypeService.selectDictDataByType("sys_heat_meter");
        if (org.apache.commons.collections.CollectionUtils.isEmpty(data)) {
            return;
        }
        List<String> list = data.stream().map(SysDictData::getDictLabel).collect(Collectors.toList());
        Field file;
        try {
            file = HouseholdExcelVo.class.getDeclaredField("buildHeatMeter");
            ExcelUtil.setCombo(file, list);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void setPlotUnitHeatMeter() {
        List<SysDictData> data = dictTypeService.selectDictDataByType("sys_heat_meter");
        if (org.apache.commons.collections.CollectionUtils.isEmpty(data)) {
            return;
        }
        List<String> list = data.stream().map(SysDictData::getDictLabel).collect(Collectors.toList());
        Field file;
        try {
            file = HouseholdExcelVo.class.getDeclaredField("plotHeatMeter");
            ExcelUtil.setCombo(file, list);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void setBuildUnitHeatMeter() {
        List<SysDictData> data = dictTypeService.selectDictDataByType("sys_heat_meter");
        if (org.apache.commons.collections.CollectionUtils.isEmpty(data)) {
            return;
        }
        List<String> list = data.stream().map(SysDictData::getDictLabel).collect(Collectors.toList());
        Field file;
        try {
            file = HouseholdExcelVo.class.getDeclaredField("unitHeatMeter");
            ExcelUtil.setCombo(file, list);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void setHeatingOrNot() {
        List<String> list = Arrays.asList("是", "否");
        Field file;
        try {
            file = HouseholdExcelVo.class.getDeclaredField("heatingOrNot");
            ExcelUtil.setCombo(file, list);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
